package com.zoe.onelink.business.entity;

import cn.hutool.core.util.StrUtil;
import com.zoe.onelink.util.ValidUtil;
import org.apache.commons.lang3.StringUtils;

import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * <p>标题: 高级查询</p>
 * <p>描述: 旧架构功能迁移过来的</p>
 * <p>版权: Copyright (c) 2019</p>
 * <p>公司: 智业软件股份有限公司</p>
 *
 * @version: 1.0
 * @author: chenweibin
 * @date 2019/11/18
 */
@Deprecated
public class AdvanceQuery {

    private static final ThreadLocal<AdvanceQuery> CURRENT_LOCAL = new ThreadLocal<>();

    private static final Pattern DATE_PATTERN = Pattern.compile("\\d{4}-\\d{1,2}-\\d{1,2} \\d{1,2}:\\d{1,2}:\\d{1,2}");

    /**
     * 排序条件
     */
    private List<Order> orders;

    /**
     * 排序时增加RowId
     */
    private boolean orderByRowId;

    /**
     * 检索条件
     */
    private List<Condition> conditions;

    public List<Order> getOrders() {
        return orders;
    }

    public void setOrders(List<Order> orders) {
        this.orders = orders;
    }

    public List<Condition> getConditions() {
        return conditions;
    }

    public void setConditions(List<Condition> conditions) {
        this.conditions = conditions;
    }

    public static void set(AdvanceQuery advanceQuery) {
        CURRENT_LOCAL.set(advanceQuery);
    }

    public static AdvanceQuery get() {
        return CURRENT_LOCAL.get();
    }

    public static synchronized AdvanceQuery getAndRemove() {
        AdvanceQuery advanceQuery = CURRENT_LOCAL.get();
        CURRENT_LOCAL.remove();
        return advanceQuery;
    }

    public static void remove() {
        CURRENT_LOCAL.remove();
    }

    public String getSql(String sql, boolean changeLine) {
        StringBuilder sb = new StringBuilder();
        sb.append("select temp.* from (").append(sql).append(") temp ");
        if (conditions != null && !conditions.isEmpty()) {

            Map<Integer, List<Condition>> listMap = conditions.stream()
                    // 初始化，给关系和分组默认值
                    .peek((item) -> {
                        if (item.getGroup() == null) {
                            item.setGroup(0);
                        }
                        if (StringUtils.isBlank(item.getRelation())) {
                            item.setRelation("and");
                        }
                    })
                    // 过滤掉不合理的
                    .filter((item) -> {
                        if (StringUtils.isBlank(item.getKey()) || StringUtils.isBlank(item.getValue())) {
                            if ((!Objects.equals("no", item.getOp()) && !Objects.equals("yes", item.getOp()))) {
                                return false;
                            }
                        }
                        return Objects.equals("AND", item.getRelation().toUpperCase()) || Objects.equals("OR", item.getRelation().toUpperCase());
                    })
                    // 按分组收集
                    .collect(Collectors.groupingBy(Condition::getGroup, Collectors.toList()));
            boolean isMoreGroup = false;
            if (listMap.size() > 0) {
                sb.append("where 1=1 and ");
                if (listMap.size() > 1) {
                    isMoreGroup = true;
                }
                String relation = "";
                int index = 0;
                for (Integer group : listMap.keySet()) {
                    List<Condition> conditions = listMap.get(group);
                    StringBuilder sbTemp = new StringBuilder();
                    for (int i = 0; i < conditions.size(); i++) {
                        sbTemp.append(" ");
                        Condition condition = conditions.get(i);
                        String s = condition.getSql("temp", changeLine);
                        sbTemp.append(s);
                        sbTemp.append(" ");
                        if (i == conditions.size() - 1) {
                            relation = condition.getRelation();
                        } else {
                            if (ValidUtil.isNotEmptyAndNull(s)) {
                                sbTemp.append(condition.getRelation());
                            }
                        }
                    }
                    index++;
                    if (isMoreGroup) {
                        if (sbTemp.length() > 0) {
                            sb.append("( ").append(sbTemp.toString()).append(") ");
                            if (index != listMap.size()) {
                                sb.append(relation).append(" ");
                            }
                        }
                    } else {
                        sb.append(sbTemp.toString());
                    }

                }
            }
        }

        if (orders != null && !orders.isEmpty()) {
            sb.append("order by ");
            List<String> lists = orders.stream()
                    // 过滤不合格的值
                    .filter((item) -> {
                        if (StringUtils.isBlank(item.getKey()) || StringUtils.isBlank(item.getKey())) {
                            return false;
                        }
                        return Objects.equals("DESC", item.getSort().toUpperCase()) || Objects.equals("ASC", item.getSort().toUpperCase().toUpperCase());
                    })
                    // 初始化值
                    .peek((item) -> {
                        if (changeLine) {
                            item.setKey(StrUtil.toUnderlineCase(item.getKey()).toUpperCase());
                        }
                    })
                    .map((item) -> " temp.\"" + item.getKey() + "\" " + item.getSort())
                    .collect(Collectors.toList());
            String join = String.join(",", lists);
            sb.append(join);
            // fix #2006636
            this.appendRowId(sb);
        }
        return sb.toString();
    }

    public void appendRowId(StringBuilder sb) {
        if (!this.orderByRowId) {
            return;
        }
        if (sb.indexOf("CONNECT BY PRIOR") > 0 || sb.indexOf("connect by prior") > 0) {
            return;
        }
        sb.append(",rowid");
    }

    /**
     * 条件
     */
    public static class Condition {
        /**
         * 查询字段
         */
        private String key;
        /**
         * 查询操作符
         * eq	等于	有且只有 1 个
         * neq  不等于
         * gte	大于等于	有且只有 1 个
         * lte	小于等于	有且只有 1 个
         * gt	大于	有且只有 1 个
         * lt	小于	有且只有 1 个
         * between	介于	有且只有 2 个
         * in	在..内	大于等于 1 个
         * notin 不在..内	大于等于 1 个
         * no	为空	is null
         * yes	不为空	is not null
         * like	包含	大于等于 1 个，多个 value 时，会使用 OR 构建查询条件
         * notlike 不包含	大于等于 1 个，多个 value 时，会使用 OR 构建查询条件
         * withstart 以..开头
         * withend 以..结尾
         */
        private String op;
        /**
         * 查询内容
         */
        private String value;
        /**
         * 与下一Condition检索条件与或关系：and or
         */
        private String relation;
        /**
         * 成组id
         */
        private Integer group;
        /**
         * 字段类型 （字符，数字，日期）
         */
        private String valueFormat;


        public Condition() {
        }

        public Condition(String key, String op, String value, String relation, Integer group, String valueFormat) {
            this.key = key;
            this.op = op;
            this.value = value;
            this.relation = relation;
            this.group = group;
            this.valueFormat = valueFormat;
        }

        public Condition(String key, String op, String value, String relation, Integer group) {
            this.key = key;
            this.op = op;
            this.value = value;
            this.relation = relation;
            this.group = group;
        }

        private String getSql(String tableName, boolean changeLine) {
            String result = "";
            StringBuilder sb = new StringBuilder();
            if (StringUtils.isBlank(getOp())) {
                return result;
            }

            if (changeLine) {
                setKey(StrUtil.toUnderlineCase(getKey()).toUpperCase());
            }

            // 是否抽成对象，有精力在干吧
            switch (op) {
                case "eq":
                    sb.append(getWhereEq(tableName));
                    break;
                case "neq":
                    sb.append(getWhereNeq(tableName));
                    break;
                case "gt":
                    sb.append(getWhereGt(tableName));
                    break;
                case "lt":
                    sb.append(getWhereLt(tableName));
                    break;
                case "gte":
                    sb.append(getWhereGte(tableName));
                    break;
                case "lte":
                    sb.append(getWhereLte(tableName));
                    break;
                case "between":
                    sb.append(getWhereBetween(tableName));
                    break;
                case "in":
                    sb.append(getWhereIn(tableName));
                    break;
                case "notin":
                    sb.append(getWhereNotIn(tableName));
                    break;
                case "no":
                    sb.append(getWhereNot(tableName));
                    break;
                case "yes":
                    sb.append(getWhereYes(tableName));
                    break;
                case "like":
                    sb.append(getWhereLike(tableName));
                    break;
                case "notlike":
                    sb.append(getWhereNotLike(tableName));
                    break;
                case "withstart":
                    sb.append(getWhereWithStart(tableName));
                    break;
                case "withend":
                    sb.append(getWhereWithEnd(tableName));
                    break;
                default:
                    break;

            }
            return sb.toString();
        }

        private String getWhereLike(String tableName) {
            StringBuilder sb = new StringBuilder();
            if (value == null || value.isEmpty()) {
                return "";
            }
            List<String> values = valueExchange(value);
            List<String> temp = new ArrayList<>();
            StringBuilder sbSingle;
            for (int i = 0; i < values.size(); i++) {
                sbSingle = new StringBuilder();
                sbSingle.append(tableName).append(".\"").append(getKey()).append("\" ")
                        .append("like '%").append(values.get(i)).append("%'");
                temp.add(sbSingle.toString());
            }

            String join = String.join(" OR ", temp);
            if (temp.size() > 1) {
                sb.append("( ").append(join).append(" )");
            } else {
                sb.append(join);
            }
            return sb.toString();
        }

        private String getWhereNotLike(String tableName) {
            StringBuilder sb = new StringBuilder();
            if (value == null || value.isEmpty()) {
                return "";
            }
            List<String> values = valueExchange(value);
            List<String> temp = new ArrayList<>();
            StringBuilder sbSingle;
            for (int i = 0; i < values.size(); i++) {
                sbSingle = new StringBuilder();
                sbSingle.append(tableName).append(".\"").append(getKey()).append("\" ")
                        .append("not like '%").append(values.get(i)).append("%'");
                temp.add(sbSingle.toString());
            }

            String join = String.join(" OR ", temp);
            if (temp.size() > 1) {
                sb.append("( ").append(join).append(" )");
            } else {
                sb.append(join);
            }
            return sb.toString();
        }

        private String getWhereYes(String tableName) {
            StringBuilder sb = new StringBuilder();
            sb.append(tableName).append(".\"").append(getKey()).append("\" ")
                    .append("is not null");
            return sb.toString();
        }

        private String getWhereNot(String tableName) {
            StringBuilder sb = new StringBuilder();
            sb.append(tableName).append(".\"").append(getKey()).append("\" ")
                    .append("is null");
            return sb.toString();
        }

        private String getWhereNotIn(String tableName) {
            StringBuilder sb = new StringBuilder();
            if (value == null || value.isEmpty()) {
                return "";
            }
            List<String> values = valueExchange(value);
            String temp = values.stream().map(item -> "'" + item + "'").collect(Collectors.joining(","));
            sb.append(tableName).append(".\"").append(getKey()).append("\" ")
                    .append("not in( ").append(temp).append(")");
            return sb.toString();
        }

        private String getWhereIn(String tableName) {
            StringBuilder sb = new StringBuilder();
            if (value == null || value.isEmpty()) {
                return "";
            }
            List<String> values = valueExchange(value);
            String temp = values.stream().map(item -> "'" + item + "'").collect(Collectors.joining(","));
            sb.append(tableName).append(".\"").append(getKey()).append("\" ")
                    .append("in( ").append(temp).append(")");
            return sb.toString();
        }

        private String getWhereBetween(String tableName) {
            StringBuilder sb = new StringBuilder();
            if (value == null || value.isEmpty()) {
                return "";
            }
            List<String> values = valueExchange(value);
            if (values.size() != 2) {
                return "";
            }
            sb.append(tableName).append(".\"").append(getKey()).append("\" ")
                    .append("between ").append(changeDateType(getKey(), values.get(0), valueFormat))
                    .append(" and ").append(changeDateType(getKey(), values.get(1), valueFormat));
            return sb.toString();
        }

        private String getWhereLte(String tableName) {
            StringBuilder sb = new StringBuilder();
            if (value == null || value.isEmpty()) {
                return "";
            }
            List<String> values = valueExchange(value);
            sb.append(tableName).append(".\"").append(getKey()).append("\" <= ").append(changeDateType(getKey(), values.get(0), valueFormat));
            return sb.toString();
        }

        private String getWhereGte(String tableName) {
            StringBuilder sb = new StringBuilder();
            if (value == null || value.isEmpty()) {
                return "";
            }
            List<String> values = valueExchange(value);
            sb.append(tableName).append(".\"").append(getKey()).append("\" >= ").append(changeDateType(getKey(), values.get(0), valueFormat));
            return sb.toString();
        }

        private String getWhereLt(String tableName) {
            StringBuilder sb = new StringBuilder();
            if (value == null || value.isEmpty()) {
                return "";
            }
            List<String> values = valueExchange(value);
            sb.append(tableName).append(".\"").append(getKey()).append("\" < ").append(changeDateType(getKey(), values.get(0), valueFormat));
            return sb.toString();
        }

        private String getWhereGt(String tableName) {
            StringBuilder sb = new StringBuilder();
            if (value == null || value.isEmpty()) {
                return "";
            }
            List<String> values = valueExchange(value);
            sb.append(tableName).append(".\"").append(getKey()).append("\" > ").append(changeDateType(getKey(), values.get(0), valueFormat));
            return sb.toString();
        }

        private String getWhereEq(String tableName) {
            StringBuilder sb = new StringBuilder();
            if (value == null || value.isEmpty()) {
                return "";
            }
            List<String> values = valueExchange(value);
            sb.append(tableName).append(".\"").append(getKey()).append("\" = ").append(changeDateType(getKey(), values.get(0), valueFormat));
            return sb.toString();
        }

        private String getWhereNeq(String tableName) {
            StringBuilder sb = new StringBuilder();
            if (value == null || value.isEmpty()) {
                return "";
            }
            List<String> values = valueExchange(value);
            sb.append(tableName).append(".\"").append(getKey()).append("\" <> ").append(changeDateType(getKey(), values.get(0), valueFormat));
            return sb.toString();
        }

        private String getWhereWithStart(String tableName) {
            StringBuilder sb = new StringBuilder();
            if (value == null || value.isEmpty()) {
                return "";
            }
            List<String> values = valueExchange(value);
            List<String> temp = new ArrayList<>();
            StringBuilder sbSingle;
            for (int i = 0; i < values.size(); i++) {
                sbSingle = new StringBuilder();
                sbSingle.append(tableName).append(".\"").append(getKey()).append("\" ")
                        .append("like '").append(values.get(i)).append("%'");
                temp.add(sbSingle.toString());
            }

            String join = String.join(" OR ", temp);
            if (temp.size() > 1) {
                sb.append("( ").append(join).append(" )");
            } else {
                sb.append(join);
            }
            return sb.toString();
        }

        private String getWhereWithEnd(String tableName) {
            StringBuilder sb = new StringBuilder();
            if (value == null || value.isEmpty()) {
                return "";
            }
            List<String> values = valueExchange(value);
            List<String> temp = new ArrayList<>();
            StringBuilder sbSingle;
            for (int i = 0; i < values.size(); i++) {
                sbSingle = new StringBuilder();
                sbSingle.append(tableName).append(".\"").append(getKey()).append("\" ")
                        .append("like '%").append(values.get(i)).append("'");
                temp.add(sbSingle.toString());
            }

            String join = String.join(" OR ", temp);
            if (temp.size() > 1) {
                sb.append("( ").append(join).append(" )");
            } else {
                sb.append(join);
            }
            return sb.toString();
        }

        public String getKey() {
            return key;
        }

        public void setKey(String key) {
            this.key = key;
        }

        public String getOp() {
            return op;
        }

        public void setOp(String op) {
            this.op = op;
        }

        public String getValue() {
            return value;
        }

        public void setValue(String value) {
            this.value = value;
        }

        public String getRelation() {
            return relation;
        }

        public void setRelation(String relation) {
            this.relation = relation;
        }

        public Integer getGroup() {
            return group;
        }

        public void setGroup(Integer group) {
            this.group = group;
        }

        public String getValueFormat() {
            return valueFormat;
        }

        public void setValueFormat(String valueFormat) {
            this.valueFormat = valueFormat;
        }

        private String changeDateType(String key, String value, String valueFormat) {

            switch (valueFormat) {
                case "2":
                    break;
                case "3":
                    // 判断是否为 2022-01-01 10:11:12 的格式
                    String dateFormat = DATE_PATTERN.matcher(value).find() ? "yyyy-mm-dd hh24:mi:ss" : "yyyy-mm-dd";
                    value = String.format("to_date('%s','%s')", value, dateFormat);
                    break;
                default:
                    value = "'" + value + "'";
                    break;
            }

            return value;
        }

        private List<String> valueExchange(String value) {
            return Arrays.asList(value.split(";"));
        }
    }

    /**
     * 排序条件
     */
    public static class Order {
        /**
         * 排序字段
         */
        private String key;

        /**
         * 排序方式 asc升序 desc降序
         */
        private String sort;

        public Order() {
        }

        public Order(String key, String sort) {
            this.key = key;
            this.sort = sort;
        }

        public String getKey() {
            return key;
        }

        public void setKey(String key) {
            this.key = key;
        }

        public String getSort() {
            return sort;
        }

        public void setSort(String sort) {
            this.sort = sort;
        }
    }

    public static void main(String[] args) {
        AdvanceQuery advanceQuery = new AdvanceQuery();
        List<Condition> conditions = new ArrayList<>();
        List<Order> orders = new ArrayList<>();
//        conditions.add(new Condition("id", "eq", new ArrayList<String>() {
//            {
//                this.add("1");
//            }
//        }, "or", 1));
//        conditions.add(new Condition("id", "gte", new ArrayList<String>() {
//            {
//                this.add("1");
//            }
//        }, "and", 1));
//        conditions.add(new Condition("id", "lte", new ArrayList<String>() {
//            {
//                this.add("1");
//            }
//        }, "or", 1));
//        conditions.add(new Condition("id", "between", new ArrayList<String>() {
//            {
//                this.add("1");
//                this.add("2");
//            }
//        }, "and", 1));
//        conditions.add(new Condition("id", "in", new ArrayList<String>() {
//            {
//                this.add("1");
//                this.add("2");
//            }
//        }, "or", 1));
//        conditions.add(new Condition("id", "notin", new ArrayList<String>() {
//            {
//                this.add("1");
//                this.add("2");
//            }
//        }, "and", 1));
//        conditions.add(new Condition("id", "no", new ArrayList<String>() {
//            {
//            }
//        }, "and", 1));
//        conditions.add(new Condition("id", "yes", new ArrayList<String>() {
//            {
//            }
//        }, "and", 1));
//
//        conditions.add(new Condition("id", "like", new ArrayList<String>() {
//            {
//                this.add("1");
//                this.add("2");
//            }
//        }, "or", 1));
//
//        conditions.add(new Condition("id", "eq", new ArrayList<String>() {
//            {
//                this.add("1");
//            }
//        }, "or", 2));
//        conditions.add(new Condition("id", "gte", new ArrayList<String>() {
//            {
//                this.add("1");
//            }
//        }, "and", 2));
//        conditions.add(new Condition("id", "lte", new ArrayList<String>() {
//            {
//                this.add("1");
//            }
//        }, "or", 2));
//        conditions.add(new Condition("id", "between", new ArrayList<String>() {
//            {
//                this.add("1");
//                this.add("2");
//            }
//        }, "and", 2));
//        conditions.add(new Condition("id", "in", new ArrayList<String>() {
//            {
//                this.add("1");
//                this.add("2");
//            }
//        }, "or", 2));
//        conditions.add(new Condition("id", "notin", new ArrayList<String>() {
//            {
//                this.add("1");
//                this.add("2");
//            }
//        }, "and", 2));
//        conditions.add(new Condition("id", "no", new ArrayList<String>() {
//            {
//            }
//        }, "and", 2));
//        conditions.add(new Condition("id", "yes", new ArrayList<String>() {
//            {
//            }
//        }, "and", 2));
//
//        conditions.add(new Condition("id", "like", new ArrayList<String>() {
//            {
//                this.add("1");
//                this.add("2");
//            }
//        }, "and", 2));

//        orders.add(new Order("id", "desc"));
//        orders.add(new Order("id1", "asc"));
//        orders.add(new Order("id2", "desc"));
//
//        advanceQuery.setConditions(conditions);
//        advanceQuery.setOrders(orders);
//        System.out.println(JSON.toJSONString(advanceQuery));
//        System.out.println(advanceQuery.getSql("select * from dual"));
        String s = "a;";
        System.out.println(Arrays.asList(s.split(";")));
    }


}


